package com.lovcreate.Controller;

import com.lovcreate.bean.Student;
import com.lovcreate.utils.ExcelParser;
import com.lovcreate.utils.XlsParser;
import com.lovcreate.utils.XlsxParser;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * Student表
 * Created by Administrator on 2016/7/27.
 */
@WebServlet(name = "ExcelToDBServlet", urlPatterns = {"/ExcelToDBServlet"})
public class ExcelToDBServlet extends HttpServlet {

    String path = "";

    @Override
    public void init() throws ServletException {
        // 路径
        path = System.getProperty("user.dir");
        path = getServletContext().getRealPath("/");;
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html; charset=utf-8");
        PrintWriter out = response.getWriter();
        out.println("<h1>Hello DB, I'm Excel ! </h1>");

        // 文件路径，根据上传文件获得(暂时用的是绝对路径，需改)
        String filePath = "F:\\hGitProject\\POIWeb\\web\\excelData\\excelData\\student_info.xls";
        // 取得文件名的后缀
        String suffix = filePath.substring(filePath.lastIndexOf(".") + 1, filePath.length());
        System.out.println(suffix);

        ExcelParser excelParser;
        excelParser = new XlsParser();      // 默认是 .xls 文件，文件类型不对要加响应处理 ！
        if("xls".equalsIgnoreCase(suffix)) {
            excelParser = new XlsParser();
        }else if("xlsx".equalsIgnoreCase(suffix)) {
            excelParser = new XlsxParser();
        } else {
            // 加重新上传文件的操作
            out.println("文件类型不正确，您需要传输后缀名为xls或者xlsx的Excel文件 !");
        }

        List<Map<String,String>> list = excelParser.excelParser(filePath);
        System.out.println(list);
        Connection conn = (Connection) getServletContext().getAttribute("conn");    // 通过上下文属性获得数据库连接
        String sql = "insert into student_info(no,name,age,score) values(?,?,?,?)";
        PreparedStatement ps = null;
        try {
            for(int i = 0; i < list.size(); i++) {
                ps = conn.prepareStatement(sql);
                Map map = list.get(i);
                for(Object key : map.keySet()) {
                    String value = map.get(key).toString();
                    if(key.toString().equals("no")) {
                        ps.setString(1,value);
                    } else if(key.toString().equals("name")) {
                        ps.setString(2,value);
                    } else if(key.toString().equals("age")) {
                        ps.setString(3,value);
                    } else if(key.toString().equals("score")) {
                        ps.setString(4,value);
                    }
                }
                boolean flag = ps.execute();        // flag值为 false 为语句插入成功
                if(!flag) {
                    // 语句执行成功

                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(ps != null) {
                try{
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }

}
